import frappe
from frappe import _
from erpnext.controllers.queries import employee_query as standard_employee_query


@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def defect_code_query(doctype, txt, searchfield, start, page_len, filters):
	operation = filters.get("operation")
	
	table = frappe.qb.DocType("Operation Assigned Defect Code")

	query = frappe.qb.from_(table
	).select(
		table.defect_code
	).where(
		(table.parent == operation)
	)
	if txt:
		query = query.where(table.defect_code.contains(txt))
	
	data = query.run()

	return data


@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def employee_query(doctype, txt, searchfield, start, page_len, filters):
	operation = filters.pop("operation", "")

	table = frappe.qb.DocType("Assigned Employee")
	
	allowed_employees = frappe.qb.from_(table
	).select(table.employee
	).where(table.parent == operation
	).run(pluck="employee")
	if allowed_employees:
		filters["name"] = ["in", allowed_employees]
	return standard_employee_query(doctype=doctype, txt=txt, 
		searchfield=searchfield, start=start, page_len=page_len, filters=filters)

@frappe.whitelist()
def get_item_custom_field(doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None):
    
    filters = {
        'dt': 'Item',
        'fieldtype': ('in', ['Data', 'Link', 'Select', 'Small Text', 'Dynamic Link']),        
    }
    or_filters = {}
    if txt:
        or_filters['label'] = ('like', f'%{txt}%')
        or_filters['fieldname'] = ('like', f'%{txt}%')
    data = frappe.get_all('Custom Field', filters = filters, or_filters = or_filters, fields = ["name", "fieldname", "label"], as_list=1)
    # 翻译标签
    data = [[d[0], d[1], _(d[2])] for d in data]
    
    return data

@frappe.whitelist()
def get_initial_work_order_batch_no(doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None):
    initial_work_order = filters.get("initial_work_order")

    se = frappe.qb.DocType("Stock Entry")
    sed = frappe.qb.DocType("Stock Entry Detail")

    batch_nos = frappe.qb.from_(se
    ).join(sed
    ).on(se.name == sed.parent
    ).where(
        (se.initial_work_order == initial_work_order) &
        (se.docstatus == 1) &
        (sed.batch_no.notnull())
    ).select(sed.batch_no
    ).distinct(
    ).run()
    
    return batch_nos
